SET NOCOUNT ON;
go
/*********************************************************************************************************
*Created/Source by: sfibich
*ScriptName: Get_TraceMemoryWarnings.sql
*Version: 1.0.2013.1.2
*SQL Versions: 2008R2
*Purpose: Report on the number of hash and sort warnings found in the default trace, by host, application
*			login, and database.  Displaying the first occurrence and last occurrence.
*					 
*********************************************************************************************************/
go
declare @filename nvarchar(256)
select
@filename=convert(nvarchar(256),tg.value)
from
fn_trace_getinfo(default) as [tg]
where
tg.traceid =1
and
tg.property = 2

select
tc.name as [Category_Name]
,te.name as [Event_Name]
,tg.HostName
,tg.ApplicationName
,tg.LoginName
,tg.DatabaseName
,COUNT(*) as [Row_Counts]
,MIN(startTime) as [Min_Start_Time]
,MAX(startTime) as [Max_Start_Time]
from
fn_trace_gettable(@filename,NULL) as [tg]
inner join
sys.trace_events te
on
tg.EventClass =te.trace_event_id
inner join
sys.trace_categories tc
on
tc.category_id =te.category_id
where
te.Name in (
'Sort Warnings'
,'Hash Warning'
)
group by
tc.name
,te.name
,tg.HostName
,tg.ApplicationName
,tg.LoginName
,tg.DatabaseName
order by
tc.name
,te.name
,tg.HostName
,tg.ApplicationName
,tg.LoginName
,tg.DatabaseName
